<?php
error_reporting(E_ALL);
$mysqli = new mysqli('MYSQL_HOST', 'MYSQL_USER', 'MYSQL_PASSWORD', 'MYSQL_DATABASE');
$limit = 5; // how many bugs to fetch+insert per run?

/*
DROP TABLE IF EXISTS `bugtracker`;
CREATE TABLE `bugtracker` (
  `bug_id` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
  `author` VARCHAR(26) NOT NULL DEFAULT '',
  `author_seo` VARCHAR(26) NOT NULL DEFAULT '',
  `date` INT(10) NOT NULL DEFAULT '0',
  `summary` VARCHAR(60) NOT NULL DEFAULT '',
  `summary_seo` VARCHAR(60) NOT NULL DEFAULT '',
  `post` TEXT,
  `status` VARCHAR(24) NOT NULL DEFAULT '',
  `priority` TINYINT(1) NOT NULL DEFAULT '0',
  `branch` VARCHAR(24) NOT NULL DEFAULT '',
  `component` VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY  (`bug_id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `bugtracker_answer`;
CREATE TABLE `bugtracker_answer` (
  `id` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
  `bug_id` MEDIUMINT(9) NOT NULL DEFAULT '0',
  `author` VARCHAR(26) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
  `date` INT(10) NOT NULL DEFAULT '0',
  `post` TEXT COLLATE latin1_german1_ci,
  PRIMARY KEY  (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 ;
*/

// http://simplehtmldom.sourceforge.net/
include('./simple_html_dom.php');

// get last Bug ID from SQL table and +1
$result = $mysqli->query("SELECT bug_id FROM bugtracker ORDER BY bug_id DESC LIMIT 1");
if ($obj = $result->fetch_object()) {
	$start = $obj->bug_id +1;
} else {
	$start = 1;
}
$result->close();

// get last Bug ID in bugtracker
$page = file_get_contents("http://www.eathena.ws/board/index.php?autocom=bugtracker");
preg_match('#showbug=([0-9]*)#', $page, $matches);
$end = $matches[1];

if ($start == $end) {
	// nothing to do
	$mysqli->close();
	exit;
}

$stop = $start + $limit -1;
if ($stop > $end) $stop = $end;


$query = "INSERT INTO bugtracker(bug_id,summary,author,date,post,`status`,priority,branch,component) VALUES ";
$query .= "\n";
$query_bug_answer = "INSERT INTO bugtracker_answer(bug_id,author,date,post) VALUES ";
$query_bug_answer .= "\n";
$query_bug_answer_len = strlen($query_bug_answer); // To compare before execution


for ($bug_id = $start; $bug_id <= $stop; $bug_id++) {
	$summary = "";
	$name = "";
	$date = 0;
	$post = "";
	$status_priority = "";
	$status = "";
	$priority = 0;
	$branch = "";
	$component = "";
	
	$page = '';
	$html = getPageDom("http://www.eathena.ws/board/index.php?autocom=bugtracker&showbug=" . $bug_id, $page);
	if (preg_match('#The error returned was:</h4>[^<]*<p>([^<]*)</p>#', $page, $matches)) {
		switch ($matches[1]) {
		case 'You do not have permission to view this area!':
			$summary = '<NEED TO LOGIN>';
			break;
		case 'Incorrect use of one of the board files':
			$summary = ''; // bug report does not exist
			break;
		}

		echo ' - [' . $bug_id . '] ' . $summary . '<br />';
	} else if (preg_match('#Bug ID \#([0-9]*)#', $page)) {
		preg_match('#<title>(.*) - Bug Tracker</title>#', $page, $matches);
		$summary = htmlspecialchars_decode(str_replace(array('&#33;','&#036;','&#39;','&#092;'), array('!','$',"'","\\"), $matches[1]));
		
		preg_match('#<strong>Status:</strong></td>[^<]*<td class=\'row2\' width=\'40%\' align=\'left\'>([^<]*)</td>#', $page, $matches);
		$status_priority = str_replace("\n", "##", trim($matches[1]));
		preg_match('/(.*)####/', $status_priority, $matches);
		$status = $matches[1];
		preg_match('/Severity: ([0-9]) -/', $status_priority, $matches);
		$priority = intval($matches[1]);
		
		preg_match('#<strong>Branch:</strong></td>[^<]*<td class=\'row2\' width=\'40%\' align=\'left\'>([^<]*)</td>#', $page, $matches);
		$branch = trim($matches[1]);
		
		preg_match('#<strong>Project:</strong></td>[^<]*<td class=\'row2\' width=\'10%\' style=\'border-right:1px solid black\' align=\'left\'>([^<]*)</td>#', $page, $matches);
		$component = trim($matches[1]);
	}
	
	// Get all names, dates and posts
	$names = array_values($html->find('span[class=normalname]'));
	$dates = array_values($html->find('span[class=postdetails]'));
	$posts = array_values($html->find('div[class=postcolor]'));
	
	// Get first entry
	$name = trim($names[0]->plaintext);
	$date = strtotime(trim($dates[0]->plaintext));
	$post = cleanPost($posts[0]->innertext);
	
	$query .= sprintf("(%d,'%s','%s',%d,'%s','%s',%d,'%s','%s')%s\n", 
	                  $bug_id, $mysqli->real_escape_string($summary), $mysqli->real_escape_string($name), $mysqli->real_escape_string($date), 
	                  $mysqli->real_escape_string($post), $mysqli->real_escape_string($status), $priority, $mysqli->real_escape_string($branch), $mysqli->real_escape_string($component), 
	                  ($bug_id == $stop ? ";" : ","));
	
	// All following answers
	if ($dates) {
		echo ' - [' . $bug_id . '] <span style="color: #008800;">added [' . (count($names) - 1) . ' answers]</span><br />';

		for ($i = 1; $i < count($names); $i++) {
			$name = trim($names[$i]->plaintext);
			$date = strtotime(trim($dates[$i * 3]->plaintext)); // Every 3th is the date
			$post = cleanPost($posts[$i]->innertext);
			$query_bug_answer .= sprintf("(%d,'%s',%d,'%s'),\n", $bug_id, $mysqli->real_escape_string($name), $mysqli->real_escape_string($date), $mysqli->real_escape_string($post));
		}
	}


}

// header("Content-Type: text/plain");
// echo $query . "\n";
// echo $query_bug_answer . "\n";

$mysqli->query($query);


// if we're done importing bugs, calculate SEO fields
if ($stop == $end) {
	define("IPS_DOC_CHAR_SET", 'UTF-8');
	include './core.php';
	
	// update SEO author
	if ($result = $mysqli->query("SELECT author FROM bugtracker WHERE `date`!=0 GROUP BY author")) {
		while ($obj = $result->fetch_object()) {
			$mysqli->query(sprintf("UPDATE bugtracker SET author_seo='%s' WHERE author='%s'", 
				$mysqli->real_escape_string(IPSText::makeSeoTitle($obj->author)), $mysqli->real_escape_string($obj->author) ));
		}
		$result->close();
	}
	
	// update SEO title
	if ($result = $mysqli->query("SELECT bug_id,summary FROM bugtracker WHERE `date`!=0")) {
		while ($obj = $result->fetch_object()) {
			$mysqli->query(sprintf("UPDATE bugtracker SET summary_seo='%s' WHERE bug_id=%d", 
				$mysqli->real_escape_string(IPSText::makeSeoTitle($obj->summary)), $obj->bug_id ));
		}
		$result->close();
	}
}

$mysqli->close();
exit;


function getPageDom($url, &$content = '')
{
	$content = file_get_contents($url);
	$dom = new simple_html_dom($content, true, true, DEFAULT_TARGET_CHARSET, DEFAULT_BR_TEXT);

	return $dom;
}

function cleanPost($post)
{
	// Remove attachment comments
	$post = preg_replace("/<\!--IBF.ATTACHMENT_REPLY_[0-9]+-->/i", "", $post);

	return $post;
}
?>
